<?php

namespace App\Exports;

use App\Services\Designer\Model\Performance;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

class PerformanceListExport implements FromCollection, WithEvents , WithStrictNullComparison
{
    use Exportable;

    protected $params;
    protected $count;

    public function __construct(array $params)
    {
        $this->params = $params;
    }

    public function collection()
    {
        // 这里使用 collect 方法生成 collection，通常模型取出的数据即为 collection 格式
        $collection = new Collection($this->createData());
        return $collection;
    }

    protected function createData()
    {
        $heading = [
            ['绩效报表数据列表','','','','','','','',''],
            ['数据导出日期：'.date('Y年m月d日'),'','','','','','','',''],
            ['设计师','专卖店','单号','申请时间','订单完成时间','订单星级','费用类型','收费等级','套内面积','订单平方数','绩效','下单时间','下单平方数','下单绩效','C单客诉时间','C单金额','C单绩效','备注'],
        ];
        $data = $this->queryData($this->params);
        $this->count = count($data);

        return array_merge($heading, $data);
    }

    protected function queryData($request)
    {

        $erp_user_id = $request['des_id'] ?? '';
        $shop_name = $request['shop_name'] ?? '';
        $order_level = $request['order_level'] ?? '';
        $cost_category = $request['cost_category'] ?? '';
        $charge_level = $request['charge_level'] ?? '';
        $size = $request['size'] ?? 10;
        $sort = $request['sort'] ?? 'sort';
        $order = $request['order'] ?? 'asc';
        $min = $request['min'] ?? '';
        $max = $request['max'] ?? '';

        $erp_user_id = $request['des_id'] ?? '';
        $shop_name = $request['shop_name'] ?? '';
        $order_level = $request['order_level'] ?? '';
        $cost_category = $request['cost_category'] ?? '';
        $charge_level = $request['charge_level'] ?? '';
        $size = $request['size'] ?? 10;
        $sort = $request['sort'] ?? 'sort';
        $order = $request['order'] ?? 'asc';
        $min = $request['min'] ?? '';
        $max = $request['max'] ?? '';
        $xiadanmin = $request['xiadanmin'] ?? '';
        $xiadanmax = $request['xiadanmax'] ?? '';
        $onum = $request['onum'] ?? '';
        $datas = Performance::query()
            ->when($erp_user_id, function ($query) use ($erp_user_id) {
                return $query->where('erp_user_id', $erp_user_id);
            })
            ->when($shop_name, function ($query) use ($shop_name) {
                return $query->where('shop_name', 'like', '%' . $shop_name . '%');
            })
            ->when($order_level, function ($query) use ($order_level) {
                return $query->where('order_level',$order_level);
            })
            ->when($cost_category, function ($query) use ($cost_category) {
                return $query->where('cost_category',$cost_category);
            })
            ->when($charge_level, function ($query) use ($charge_level) {
                return $query->where('charge_level',$charge_level);
            })
            ->when($onum, function ($query) use ($onum) {
                return $query->where('onum',$onum);
            })
            ->when($min && $max, function ($query) use ($min, $max) {
                return $query->where('time', '>=', $min)
                    ->where('time', '<=', $max);
            })
            ->when($xiadanmin && $xiadanmax, function ($query) use ($xiadanmin, $xiadanmax) {
                return $query->where('place_order_time', '>=', $xiadanmin)
                    ->where('place_order_time', '<=', $xiadanmax);
            })
            ->orderBy($sort, $order)
            ->orderBy('time', 'DESC')
            ->orderBy("updated_at", "desc")
            ->get()->toArray();

        $res = [];
        foreach ($datas as $k=>$datum)
        {
            $res[$k][]= $datum['designer_name'];
            $res[$k][]= $datum['shop_name'];
            $res[$k][]= $datum['onum'];
            $res[$k][]= $datum['time'];
            $res[$k][]= $datum['audit_time'];
            $res[$k][]= $datum['order_level'];
            $res[$k][]= $datum['cost_category'];
            $res[$k][]= $datum['charge_level'];
            $res[$k][]= $datum['inside_space'];
            $res[$k][]= $datum['order_square'];
            $res[$k][]= $datum['performance'];
            $res[$k][]= $datum['place_order_time'];
            $res[$k][]= $datum['place_order_square'];
            $res[$k][]= $datum['place_order_performance'];
            $res[$k][]= $datum['c_complaint_time'];
            $res[$k][]= $datum['c_order_money'];
            $res[$k][]= $datum['c_order_performance'];
            $res[$k][]= $datum['remark'];
        }

        return $res;
    }

    /**
     * 注册事件
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class  => function(AfterSheet $event) {
                //设置列宽
                $event->sheet->getDelegate()->getColumnDimension('A')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('B')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('C')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('D')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('E')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('F')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('G')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('H')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('I')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('J')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('K')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('L')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('M')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('N')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('O')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('P')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('Q')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('R')->setWidth(20);
                //设置区域单元格字体、颜色、背景等，其他设置请查看 applyFromArray 方法，提供了注释
                $event->sheet->getDelegate()->getStyle('A1:R1')->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'font' => [
                        'name' => '等线',
                        'size' => '22',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                    ],
                ]);
                $event->sheet->getDelegate()->getStyle('A2:R2')->applyFromArray([
                    'font' => [
                        'name' => 'Arial',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                        'color' => [
                            'rgb' => 'FF0000'
                        ]
                    ]
                ]);
                $event->sheet->getDelegate()->getStyle('A3:R3')->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'font' => [
                        'name' => 'Arial',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                        'color' => [
                            'rgb' => '4472C4'
                        ]
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => [
                                'rgb' => '808080'
                            ]
                        ]
                    ],
                    'fill' => [
                        'fillType' => 'linear', //线性填充，类似渐变
                        'rotation' => 45, //渐变角度
                        'startColor' => [
                            'rgb' => 'F2F2F2' //初始颜色
                        ],
                        //结束颜色，如果需要单一背景色，请和初始颜色保持一致
                        'endColor' => [
                            'argb' => 'F2F2F2'
                        ]
                    ],
                ]);
                $event->sheet->getDelegate()->getStyle('A4:R'.(3+$this->count))->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => [
                                'rgb' => '808080'
                            ]
                        ]
                    ],
                ]);

                //合并单元格
                $event->sheet->getDelegate()->mergeCells('A1:R1');
                $event->sheet->getDelegate()->mergeCells('A2:R2');
            }
        ];
    }
}
